﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Configuration;

namespace SQLServerDAL
{
    public class ChargeDAL : IDAL.IChargeDAL
    {
        //根据用户名获得用户ID
        public int getUserId(String username)
        {
            int id = -1;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT user_id FROM UserDetail_Tab WHERE userDetail_name ='" + username + "'";
            dal.ExecuteReader();
            try{
            if (dal.Reader.Read())
            {
                id=dal.Reader.GetInt32(0);
            }
            }
            catch (SqlException e)
            {
                id = -1;
            }

            return id;
        }

        //根据卡号获得卡ID
        public int getCardId(String cardList_num)
        {
            int id = -1;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT cardList_id FROM CardList_Tab WHERE cardList_num = '" + cardList_num + "'";
            try{
                dal.ExecuteReader();
                if (dal.Reader.Read())
                {
                    id=dal.Reader.GetInt32(0);
                }
            }catch(SqlException e)
            {
                id = -1;
            }
        //else
        //{
                
        //    throw new Exception("No cardId with the given cardnum is found");
        //}

            return id;
        }
       
        //判断卡号是否有效，有效时充值
        // return 0:充值成功；1:充值卡不存在；2:充值卡已被使用；3:卡没有卖出；4:充值失败
        public int Charge(String username, String cardList_num) 
        {
            int rtInt = 4;
            //根据卡号获得卡ID
            int cardList_id = getCardId(cardList_num);
            //根据用户名获得用户ID
            int user_id = getUserId(username);
            if (cardList_id > 0)
            {
                SQLServerDAL dal = new SQLServerDAL();
                dal.CommandText = "SELECT * FROM CardList_Tab WHERE cardList_id =" + cardList_id;
                dal.ExecuteReader();
        
                CardList tempCrad = null;
                if (dal.Reader.Read())
                {
                    tempCrad = new CardList();
                    string createDate= dal.Reader.GetString(2);
                    int cardTypeId= dal.Reader.GetInt32(3);
                    Boolean used = dal.Reader.GetBoolean(4);
                    int stateId = dal.Reader.GetInt32(5);
                    if (used == true)
                    {
                        rtInt = 2;//卡已使用
                    }
                    else if (stateId == 0)
                    {
                        rtInt = 3;//卡没有卖出
                    }
                    else 
                    { 
                        //try
                        //{
                            SQLServerDAL dal1 = new SQLServerDAL();
                            dal1.CommandText = "UPDATE CardList_Tab SET cardList_used=1 WHERE cardList_id=" + cardList_id;
                            dal1.ExecuteNonQuery();


                            SQLServerDAL dal2 = new SQLServerDAL();
                            dal2.CommandText = "INSERT INTO Charge_Log(charge_userId,charge_cardId,charge_typeId,charge_date) VALUES("+user_id+ ","+cardList_id+","+cardTypeId+",'"+DateTime.Now.ToString()+"')";
                            dal2.ExecuteNonQuery();

                            SQLServerDAL dal3 = new SQLServerDAL();
                            dal3.CommandText = "SELECT cardType_validPeriod FROM CardType_Dic WHERE cardType_id =" + cardTypeId;
                            dal3.ExecuteReader();
                            dal3.Reader.Read();

                            SQLServerDAL dal4 = new SQLServerDAL();
                            dal4.CommandText = "UPDATE UserDetail_Tab SET userDetail_leftDayNum = userDetail_leftDayNum + " + dal3.Reader["cardType_validPeriod"] + " WHERE user_id = " + user_id;
                            dal4.ExecuteNonQuery();

                            rtInt = 0;//充值成功
                        //}
                            //catch (Exception e)
                            //{
                              //  rtInt = 4;//充值失败
                                
                            //}
                        
                    }
                }
                         

            } 
            else
                {
                    rtInt = 1;//充值卡不存在
                }     
            return rtInt;
        }

        public string getcardNum()
        {
            string card_num = "";
            SQLServerDAL cardNum = new SQLServerDAL();
            cardNum.CommandText = "SELECT TOP 1 cardList_num, cardList_id FROM CardList_Tab WHERE cardList_used=0 AND cardList_stateID = 0";
            cardNum.ExecuteReader();

            if (cardNum.Reader.Read())
            {
                card_num = cardNum.Reader["cardList_num"].ToString();

                SQLServerDAL cardNum2 = new SQLServerDAL();
                cardNum2.CommandText = "UPDATE CardList_Tab SET cardList_stateID = 1 WHERE cardList_id = " + cardNum.Reader["cardList_id"];
                cardNum2.ExecuteNonQuery();
            }
            else
            {
                card_num = "卡已售空，请稍候再试！";
            }

            return card_num;
        }
    
    }
}

/* 
 * // return 0:充值成功,1,卡类型不存在,4:充值失败.
public int ManageCharge(int userId, int cardTypeId)
{
    int rtInt = 4;

    String sql = String.Empty;

    sql = String.Format("SELECT * FROM cardtype WHERE cardTypeId={0}", cardTypeId);
    DataTable cardTypeTable = m_SQLTools.DataTableFromSQL(sql);
    if (cardTypeTable != null)
    {
        //try
        //{
            sql = String.Format("SELECT * FROM userBalance WHERE userId={0}", userId);
            DataTable userTable = m_SQLTools.DataTableFromSQL(sql);

            int raiseDate = (int)cardTypeTable.Rows[0]["Date"];

            DateTime dt1 = Convert.ToDateTime(userTable.Rows[0]["timeOut"].ToString());
            DateTime dt2 = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
            if (DateTime.Compare(dt2, dt1) > 0)
            {
                sql = String.Format("UPDATE userBalance SET timeOut='{0}' WHERE userId={1}", DateTime.Now, userId);
                m_SQLTools.ExecuteNoQuery(sql);

            }

            sql = String.Format("UPDATE userBalance SET timeOut=timeOut+{0} WHERE userId={1}", raiseDate, userId);
            m_SQLTools.ExecuteNoQuery(sql);

            sql = String.Format("INSERT INTO chargelog(userId,cardId,chargeTypeId,chargeTime) VALUES({0},{1},{2},'{3}')", userId, -1, cardTypeId, DateTime.Now);
            m_SQLTools.ExecuteNoQuery(sql);

            rtInt = 0;
        //}
        //catch (Exception e)
        //{
        //    rtInt = 4;
        //}
    }
    else
    {
        rtInt = 1;
    }

 * 
 * return 0:充值成功,1:充值卡不存在,2:充值卡已被使用,3:充值卡已过期,4:充值失败
public int Charge(int userId, String cardNumber)
{
    int rtInt = 4;
    int cardId = getCardId(cardNumber);
    if (cardId > 0)
    {
        String sql = String.Empty;
        sql = String.Format("SELECT * FROM cards WHERE cardId={0}", cardId);
        DataTable cardTable = m_SQLTools.DataTableFromSQL(sql);
        int used = (int)cardTable.Rows[0]["used"];
        DateTime validDate = (DateTime)cardTable.Rows[0]["validDate"];
        int cardTypeId = (int)cardTable.Rows[0]["cardTypeId"];

        if (used == 1)
        {
            rtInt = 2;
        }
        else if (validDate < DateTime.Now)
        {
            rtInt = 3;
        }
        else
        {
            sql = String.Format("SELECT * FROM cardtype WHERE cardTypeId={0}", cardTypeId);
            DataTable cardTypeTable = m_SQLTools.DataTableFromSQL(sql);
            if (cardTypeTable != null)
            {
                try
                {
                    sql = String.Format("SELECT * FROM userBalance WHERE userId={0}", userId);
                    DataTable userTable = m_SQLTools.DataTableFromSQL(sql);

                    int raiseDate = (int)cardTypeTable.Rows[0]["Date"];

                    DateTime dt1 = Convert.ToDateTime(userTable.Rows[0]["timeOut"].ToString());
                    DateTime dt2 = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
                    if (DateTime.Compare(dt2, dt1) > 0)
                    {
                        sql = String.Format("UPDATE userBalance SET timeOut='{0}' WHERE userId={1}", DateTime.Now, userId);
                        m_SQLTools.ExecuteNoQuery(sql);

                    }

                    sql = String.Format("UPDATE cards SET used=1 WHERE cardId={0}", cardId);
                    m_SQLTools.ExecuteNoQuery(sql);

                    sql = String.Format("UPDATE userBalance SET timeOut=timeOut+{0} WHERE userId={1}", raiseDate, userId);
                    m_SQLTools.ExecuteNoQuery(sql);

                    sql = String.Format("INSERT INTO chargelog(userId,cardId,chargeTypeId,chargeTime) VALUES({0},{1},{2},'{3}')", userId, cardId, cardTypeId, DateTime.Now);
                    m_SQLTools.ExecuteNoQuery(sql);

                    rtInt = 0;
                }
                catch (Exception e)
                {
                    rtInt = 4;
                }
            }
            else
            {
                rtInt = 4;
            }

        }
    }
    else
    {
        rtInt = 1;
    }


    return rtInt;
}*/

